assignment1_Sep27

library(tidyverse)
library(plotly)
library(zoo)

6. Publish all of this work in a GitHub webpage titled “yourname_A1” (using the steps from Chapter 1.4). For this assignment, a support script is available by request from the TAs.

1 Create bar charts

1.1 Read files related to electricity consumption

Time window: From the first quarter of 2017 to the second quarter of 2021

years <- 2017:2021
quarters <- 1:4
i=1
type <- "Electric"
pge_17toNow_elec <- NULL

for (year in years){
  for(quarter in quarters){
    filename <- 
    paste0(
      "PGE_",
      year,
      "_Q",
      quarter,
      "_",
      type,
      "UsageByZip.csv"
    )
    print(filename)
    if((quarter>=3)&&(year==2021)){
      next;
      }
    else{
    temp <- read_csv(filename)
    pge_17toNow_elec <- rbind(pge_17toNow_elec,temp)
    
    saveRDS(pge_17toNow_elec, "pge_17toNow_elec.rds")}

    }
}

## [1] "PGE_2017_Q1_ElectricUsageByZip.csv"
## [1] "PGE_2017_Q2_ElectricUsageByZip.csv"
## [1] "PGE_2017_Q3_ElectricUsageByZip.csv"
## [1] "PGE_2017_Q4_ElectricUsageByZip.csv"
## [1] "PGE_2018_Q1_ElectricUsageByZip.csv"
## [1] "PGE_2018_Q2_ElectricUsageByZip.csv"
## [1] "PGE_2018_Q3_ElectricUsageByZip.csv"
## [1] "PGE_2018_Q4_ElectricUsageByZip.csv"
## [1] "PGE_2019_Q1_ElectricUsageByZip.csv"
## [1] "PGE_2019_Q2_ElectricUsageByZip.csv"
## [1] "PGE_2019_Q3_ElectricUsageByZip.csv"
## [1] "PGE_2019_Q4_ElectricUsageByZip.csv"
## [1] "PGE_2020_Q1_ElectricUsageByZip.csv"
## [1] "PGE_2020_Q2_ElectricUsageByZip.csv"
## [1] "PGE_2020_Q3_ElectricUsageByZip.csv"
## [1] "PGE_2020_Q4_ElectricUsageByZip.csv"
## [1] "PGE_2021_Q1_ElectricUsageByZip.csv"
## [1] "PGE_2021_Q2_ElectricUsageByZip.csv"
## [1] "PGE_2021_Q3_ElectricUsageByZip.csv"
## [1] "PGE_2021_Q4_ElectricUsageByZip.csv"

1.2 Read files related to gas consumption

Time window: From the first quarter of 2017 to the second quarter of 2021

years <- 2017:2021
quarters <- 1:4
i=1
type <- "Gas"
pge_17toNow_gas <- NULL

for (year in years){
  for(quarter in quarters){
    filename <- 
    paste0(
      "PGE_",
      year,
      "_Q",
      quarter,
      "_",
      type,
      "UsageByZip.csv"
    )
    print(filename)
    if((quarter>=3)&&(year==2021)){
      next;
      }
    else{
    temp <- read_csv(filename)
    pge_17toNow_gas <- rbind(pge_17toNow_gas,temp)
    
    saveRDS(pge_17toNow_gas, "pge_17toNow_gas.rds")}
    }
}

## [1] "PGE_2017_Q1_GasUsageByZip.csv"
## [1] "PGE_2017_Q2_GasUsageByZip.csv"
## [1] "PGE_2017_Q3_GasUsageByZip.csv"
## [1] "PGE_2017_Q4_GasUsageByZip.csv"
## [1] "PGE_2018_Q1_GasUsageByZip.csv"
## [1] "PGE_2018_Q2_GasUsageByZip.csv"
## [1] "PGE_2018_Q3_GasUsageByZip.csv"
## [1] "PGE_2018_Q4_GasUsageByZip.csv"
## [1] "PGE_2019_Q1_GasUsageByZip.csv"
## [1] "PGE_2019_Q2_GasUsageByZip.csv"
## [1] "PGE_2019_Q3_GasUsageByZip.csv"
## [1] "PGE_2019_Q4_GasUsageByZip.csv"
## [1] "PGE_2020_Q1_GasUsageByZip.csv"
## [1] "PGE_2020_Q2_GasUsageByZip.csv"
## [1] "PGE_2020_Q3_GasUsageByZip.csv"
## [1] "PGE_2020_Q4_GasUsageByZip.csv"
## [1] "PGE_2021_Q1_GasUsageByZip.csv"
## [1] "PGE_2021_Q2_GasUsageByZip.csv"
## [1] "PGE_2021_Q3_GasUsageByZip.csv"
## [1] "PGE_2021_Q4_GasUsageByZip.csv"

1.3 Obtain a table about residential electricity consumption

Assumption: 1 kBtu=3.412 KWH

pge_final_elec_Residential <-
  pge_17toNow_elec %>% 
  filter(
    CUSTOMERCLASS %in% 
      c(
        "Elec- Residential"
      )
  ) %>% 
  select(
    -c(COMBINED, AVERAGEKWH)
  ) %>% 
  group_by(YEAR, MONTH) %>% 
  summarize(
    TOTALKWH = 
      sum(
        TOTALKWH, 
        na.rm = T
      )
  ) %>% 
  mutate(
    Residential_Electric_kBtu=3.412*TOTALKWH,
    Time=as.Date(as.yearmon(paste0(YEAR,'-',MONTH)))
  )
pge_final_elec_Residential

## # A tibble: 54 × 5
## # Groups:   YEAR [5]
##     YEAR MONTH   TOTALKWH Residential_Electric_kBtu Time      
##    <dbl> <dbl>      <dbl>                     <dbl> <date>    
##  1  2017     1 2696632298               9200909401. 2017-01-01
##  2  2017     2 2089405296               7129050870. 2017-02-01
##  3  2017     3 2007875145               6850869995. 2017-03-01
##  4  2017     4 1838107707               6271623496. 2017-04-01
##  5  2017     5 2070656073               7065078521. 2017-05-01
##  6  2017     6 2615607826               8924453902. 2017-06-01
##  7  2017     7 3113124827              10621981910. 2017-07-01
##  8  2017     8 2995761408              10221537924. 2017-08-01
##  9  2017     9 5072659238              17307913320. 2017-09-01
## 10  2017    10 1956786021               6676553904. 2017-10-01
## # … with 44 more rows

1.4 Obtain a table about residential gas consumption

Assumption: 1 kBtu=100.00039 THM

pge_final_gas_Residential <-
  pge_17toNow_gas %>% 
    filter(
      CUSTOMERCLASS %in% 
        c(
          "Gas- Residential"
        )
    ) %>% 
    select(
      -c(COMBINED, AVERAGETHM)
    ) %>% 
    group_by(YEAR, MONTH) %>% 
    summarize(
      TOTALTHM = 
        sum(
          TOTALTHM, 
          na.rm = T
        )
    ) %>% 
    mutate(
      Residential_Gas_kBtu=100.00039*TOTALTHM,
      Time=as.Date(as.yearmon(paste0(YEAR,'-',MONTH)))
    )
pge_final_gas_Residential

## # A tibble: 54 × 5
## # Groups:   YEAR [5]
##     YEAR MONTH  TOTALTHM Residential_Gas_kBtu Time      
##    <dbl> <dbl>     <dbl>                <dbl> <date>    
##  1  2017     1 332986863         33298816165. 2017-01-01
##  2  2017     2 214756795         21475763255. 2017-02-01
##  3  2017     3 167698331         16769898502. 2017-03-01
##  4  2017     4 128910844         12891134675. 2017-04-01
##  5  2017     5  93587412          9358777699. 2017-05-01
##  6  2017     6  74050205          7405049380. 2017-06-01
##  7  2017     7  70934300          7093457664. 2017-07-01
##  8  2017     8  69535451          6953572219. 2017-08-01
##  9  2017     9 136754090         13675462334. 2017-09-01
## 10  2017    10  92544327          9254468792. 2017-10-01
## # … with 44 more rows

1.5 Obtain a combined data set of electricity consumption and gas consumption

1. List the electricity and gas consumption of each month from the first quarter of 2017 to the second quarter of 2021 in chronological order.

2. Take kBtu as the unit for statistics.

pge_final_GasAndElec_Residential<-
  merge(pge_final_gas_Residential,pge_final_elec_Residential,by=c("YEAR","MONTH","Time"))
  arrange(pge_final_GasAndElec_Residential,pge_final_GasAndElec_Residential$YEAR,pge_final_GasAndElec_Residential$MONTH)

##    YEAR MONTH       Time  TOTALTHM Residential_Gas_kBtu   TOTALKWH
## 1  2017     1 2017-01-01 332986863          33298816165 2696632298
## 2  2017     2 2017-02-01 214756795          21475763255 2089405296
## 3  2017     3 2017-03-01 167698331          16769898502 2007875145
## 4  2017     4 2017-04-01 128910844          12891134675 1838107707
## 5  2017     5 2017-05-01  93587412           9358777699 2070656073
## 6  2017     6 2017-06-01  74050205           7405049380 2615607826
## 7  2017     7 2017-07-01  70934300           7093457664 3113124827
## 8  2017     8 2017-08-01  69535451           6953572219 2995761408
## 9  2017     9 2017-09-01 136754090          13675462334 5072659238
## 10 2017    10 2017-10-01  92544327           9254468792 1956786021
## 11 2017    11 2017-11-01 156152999          15615360800 2094280651
## 12 2017    12 2017-12-01 292245699          29224683876 2570202596
## 13 2018     1 2018-01-01 256574528          25657552864 2394606529
## 14 2018     2 2018-02-01 225070744          22507162178 1995117494
## 15 2018     3 2018-03-01 216716670          21671751520 2132205702
## 16 2018     4 2018-04-01 126723543          12672403722 1787407410
## 17 2018     5 2018-05-01  99480004           9948039197 1884178568
## 18 2018     6 2018-06-01  77327887           7732818858 2317072842
## 19 2018     7 2018-07-01  70491825           7049209992 3076516808
## 20 2018     8 2018-08-01  73628195           7362848215 2666565449
## 21 2018     9 2018-09-01  76877874           7687817382 2226205087
## 22 2018    10 2018-10-01  91456098           9145645468 1922505661
## 23 2018    11 2018-11-01 183442904          18344361943 2126422448
## 24 2018    12 2018-12-01 274098326          27409939498 2544937320
## 25 2019     1 2019-01-01 272036368          27203742894 2405339756
## 26 2019     2 2019-02-01 295982749          29598390333 2236397294
## 27 2019     3 2019-03-01 210599369          21060019034 2050620632
## 28 2019     4 2019-04-01 115037539          11503798765 1788322259
## 29 2019     5 2019-05-01 111158190          11115862352 1815189861
## 30 2019     6 2019-06-01  75787863           7578815857 2418454504
## 31 2019     7 2019-07-01  73241548           7324183364 2726820259
## 32 2019     8 2019-08-01  70172439           7017271267 2876356186
## 33 2019     9 2019-09-01  73713782           7371406948 2285965349
## 34 2019    10 2019-10-01 109128238          10912866360 1818498345
## 35 2019    11 2019-11-01 188281565          18828229930 2075274460
## 36 2019    12 2019-12-01 270780478          27078153404 2545111200
## 37 2020     1 2020-01-01 297296398          29729755746 2435661356
## 38 2020     2 2020-02-01 213417355          21341818733 1919849451
## 39 2020     3 2020-03-01 210543362          21054418312 2093623179
## 40 2020     4 2020-04-01 147891631          14789220778 1946442584
## 41 2020     5 2020-05-01  98138323           9813870574 2181577771
## 42 2020     6 2020-06-01  80817601           8081791619 2537131639
## 43 2020     7 2020-07-01  80943142           8094345768 2926818249
## 44 2020     8 2020-08-01  76504327           7650462537 3339220306
## 45 2020     9 2020-09-01  77963575           7796387906 2712667506
## 46 2020    10 2020-10-01  97993457           9799383917 2328434857
## 47 2020    11 2020-11-01 218223203          21822405407 2283895788
## 48 2020    12 2020-12-01 302896475          30289765630 2740849821
## 49 2021     1 2021-01-01 297380109          29738126878 2583304201
## 50 2021     2 2021-02-01 223669318          22367019031 2046094821
## 51 2021     3 2021-03-01 227459191          22746007809 2078572237
## 52 2021     4 2021-04-01 133597441          13359796203 2073885444
## 53 2021     5 2021-05-01 102778274          10277867484 2364683776
## 54 2021     6 2021-06-01  78597892           7859819853 2882773155
##    Residential_Electric_kBtu
## 1                 9200909401
## 2                 7129050870
## 3                 6850869995
## 4                 6271623496
## 5                 7065078521
## 6                 8924453902
## 7                10621981910
## 8                10221537924
## 9                17307913320
## 10                6676553904
## 11                7145685581
## 12                8769531258
## 13                8170397477
## 14                6807340890
## 15                7275085855
## 16                6098634083
## 17                6428817274
## 18                7905852537
## 19               10497075349
## 20                9098321312
## 21                7595811757
## 22                6559589315
## 23                7255353393
## 24                8683326136
## 25                8207019247
## 26                7630587567
## 27                6996717596
## 28                6101755548
## 29                6193427806
## 30                8251766768
## 31                9303910724
## 32                9814127307
## 33                7799713771
## 34                6204716353
## 35                7080836458
## 36                8683919414
## 37                8310476547
## 38                6550526327
## 39                7143442287
## 40                6641262097
## 41                7443543355
## 42                8656693152
## 43                9986303866
## 44               11393419684
## 45                9255621530
## 46                7944619732
## 47                7792652429
## 48                9351779589
## 49                8814233934
## 50                6981275529
## 51                7092088473
## 52                7076097135
## 53                8068301044
## 54                9836022005

1.6 Create bar charts showing monthly total kBTUs of residential electricity and gas consumption for the entire PG&E territory

Time window: from the first quarter of 2017 to the second quarter of 2021

plot_ly() %>% 
  add_trace(
    data = pge_final_GasAndElec_Residential,
    x = ~Time%>% factor(),
    y = ~Residential_Gas_kBtu,
    type = "bar",
    name = "Residential_Gas_Consumption"
  ) %>% 
  add_trace(
    data = pge_final_GasAndElec_Residential,
    x = ~Time %>% factor(),
    y = ~Residential_Electric_kBtu,
    type = "bar",
    name = "Residential_Electricity_Consumption"
  ) %>% 
  layout(
    legend=list(
      x = 0.1, y = 1.0,
      orientation = 'h'),
    title = "Residential gas/electricity consumption from 2017Q1 to 2021Q2", 
    xaxis = list(
      title = "Month/Year",
      fixedrange = T,
      tickformat  = "%m-%Y",
      type = "date",
      dtick = "M3"
    ),
    yaxis = list(
      title = "kBtu",
      fixedrange = T
    ),
    barmode = "stack"
  ) %>% 
  config(displayModeBar = F)

1.7 Create bar charts showing monthly total kBTUs of commercial electricity and gas consumption for the entire PG&E territory

Time window: from the first quarter of 2017 to the second quarter of 2021

• The operation steps are almost the same as 1.1-1.6, and replace ‘residential’ with ‘commercial’.

pge_final_elec_Commercial <-
  pge_17toNow_elec %>% 
  filter(
    CUSTOMERCLASS %in% 
      c(
        "Elec- Commercial"
      )
  ) %>% 
  select(
    -c(COMBINED, AVERAGEKWH)
  ) %>% 
  group_by(YEAR, MONTH) %>% 
  summarize(
    TOTALKWH = 
      sum(
        TOTALKWH, 
        na.rm = T
      )
  ) %>% 
  mutate(
    Commercial_Electric_kBtu=3.412*TOTALKWH,
    Time=as.Date(as.yearmon(paste0(YEAR,'-',MONTH)))
  )
pge_final_elec_Commercial

## # A tibble: 54 × 5
## # Groups:   YEAR [5]
##     YEAR MONTH   TOTALKWH Commercial_Electric_kBtu Time      
##    <dbl> <dbl>      <dbl>                    <dbl> <date>    
##  1  2017     1 2518133184              8591870424. 2017-01-01
##  2  2017     2 2186409895              7460030562. 2017-02-01
##  3  2017     3 2328765919              7945749316. 2017-03-01
##  4  2017     4 2260528589              7712923546. 2017-04-01
##  5  2017     5 2472760712              8437059549. 2017-05-01
##  6  2017     6 2579187737              8800188559. 2017-06-01
##  7  2017     7 2808792813              9583601078. 2017-07-01
##  8  2017     8 2925396026              9981451241. 2017-08-01
##  9  2017     9 5462119756             18636752607. 2017-09-01
## 10  2017    10 2739510203              9347208813. 2017-10-01
## # … with 44 more rows

pge_final_gas_Commercial <-
  pge_17toNow_gas %>% 
    filter(
      CUSTOMERCLASS %in% 
        c(
          "Gas- Commercial"
        )
    ) %>% 
    select(
      -c(COMBINED, AVERAGETHM)
    ) %>% 
    group_by(YEAR, MONTH) %>% 
    summarize(
      TOTALTHM = 
        sum(
          TOTALTHM, 
          na.rm = T
        )
    ) %>% 
    mutate(
      Commercial_Gas_kBtu=100.00039*TOTALTHM,
      Time=as.Date(as.yearmon(paste0(YEAR,'-',MONTH)))
    )
pge_final_gas_Commercial

## # A tibble: 54 × 5
## # Groups:   YEAR [5]
##     YEAR MONTH TOTALTHM Commercial_Gas_kBtu Time      
##    <dbl> <dbl>    <dbl>               <dbl> <date>    
##  1  2017     1 81974539         8197485870. 2017-01-01
##  2  2017     2 57175524         5717574698. 2017-02-01
##  3  2017     3 51732816         5173301776. 2017-03-01
##  4  2017     4 42328501         4232866608. 2017-04-01
##  5  2017     5 36825348         3682549162. 2017-05-01
##  6  2017     6 31689281         3168940459. 2017-06-01
##  7  2017     7 30972459         3097257979. 2017-07-01
##  8  2017     8 31294433         3129455505. 2017-08-01
##  9  2017     9 61404848         6140508748. 2017-09-01
## 10  2017    10 38024375         3802452330. 2017-10-01
## # … with 44 more rows

pge_final_GasAndElec_Commercial<-
  merge(pge_final_gas_Commercial,pge_final_elec_Commercial,by=c("YEAR","MONTH","Time"))
  arrange(pge_final_GasAndElec_Commercial,pge_final_GasAndElec_Commercial$YEAR,pge_final_GasAndElec_Commercial$MONTH)

##    YEAR MONTH       Time TOTALTHM Commercial_Gas_kBtu   TOTALKWH
## 1  2017     1 2017-01-01 81974539          8197485870 2518133184
## 2  2017     2 2017-02-01 57175524          5717574698 2186409895
## 3  2017     3 2017-03-01 51732816          5173301776 2328765919
## 4  2017     4 2017-04-01 42328501          4232866608 2260528589
## 5  2017     5 2017-05-01 36825348          3682549162 2472760712
## 6  2017     6 2017-06-01 31689281          3168940459 2579187737
## 7  2017     7 2017-07-01 30972459          3097257979 2808792813
## 8  2017     8 2017-08-01 31294433          3129455505 2925396026
## 9  2017     9 2017-09-01 61404848          6140508748 5462119756
## 10 2017    10 2017-10-01 38024375          3802452330 2739510203
## 11 2017    11 2017-11-01 47704572          4770475805 2335565416
## 12 2017    12 2017-12-01 72381506          7238178829 2300499051
## 13 2018     1 2018-01-01 68229609          6822987510 2408160360
## 14 2018     2 2018-02-01 61167040          6116727855 2155263230
## 15 2018     3 2018-03-01 62150804          6215104639 2344730348
## 16 2018     4 2018-04-01 45152645          4515282110 2149231224
## 17 2018     5 2018-05-01 41023398          4102355799 2329429451
## 18 2018     6 2018-06-01 34280865          3428099870 2484527660
## 19 2018     7 2018-07-01 32624033          3262416023 2806919508
## 20 2018     8 2018-08-01 35658808          3565894707 2788621167
## 21 2018     9 2018-09-01 36552669          3655281156 2434135150
## 22 2018    10 2018-10-01 44246751          4424692356 2583642299
## 23 2018    11 2018-11-01 61713842          6171408268 2355850896
## 24 2018    12 2018-12-01 79774028          7977433912 2318696731
## 25 2019     1 2019-01-01 85968037          8596837228 2411412566
## 26 2019     2 2019-02-01 90504731          9050508397 2175465676
## 27 2019     3 2019-03-01 75080567          7508085981 2262687638
## 28 2019     4 2019-04-01 53456159          5345636748 2340405445
## 29 2019     5 2019-05-01 54414839          5441505122 2404667002
## 30 2019     6 2019-06-01 42491414          4249157972 2532843133
## 31 2019     7 2019-07-01 42441045          4244121052 2720967851
## 32 2019     8 2019-08-01 42087847          4208801114 2899824037
## 33 2019     9 2019-09-01 43114274          4311444215 2645042957
## 34 2019    10 2019-10-01 58230677          5823090410 2493295489
## 35 2019    11 2019-11-01 70930715          7093099163 2324287717
## 36 2019    12 2019-12-01 90107471          9010782242 2330850760
## 37 2020     1 2020-01-01 99682071          9968245976 2426397547
## 38 2020     2 2020-02-01 79910368          7991067965 2190511980
## 39 2020     3 2020-03-01 72628696          7262897925 2122420247
## 40 2020     4 2020-04-01 48329101          4832928948 1872348428
## 41 2020     5 2020-05-01 39984871          3998502694 2110068025
## 42 2020     6 2020-06-01 37347568          3734771366 2254418095
## 43 2020     7 2020-07-01 41091748          4109190826 2447583729
## 44 2020     8 2020-08-01 38181884          3818203291 2652700780
## 45 2020     9 2020-09-01 38699899          3870004993 2528382120
## 46 2020    10 2020-10-01 45482939          4548311638 2467052514
## 47 2020    11 2020-11-01 70066329          7006660226 1976895565
## 48 2020    12 2020-12-01 87891802          8789214478 2011384107
## 49 2021     1 2021-01-01 87849465          8784980761 2696911775
## 50 2021     2 2021-02-01 73948118          7394840640 2408987381
## 51 2021     3 2021-03-01 80053860          8005417221 2663954984
## 52 2021     4 2021-04-01 58823491          5882372041 2819988257
## 53 2021     5 2021-05-01 50407495          5040769159 3025457057
## 54 2021     6 2021-06-01 42960752          4296091955 3141496824
##    Commercial_Electric_kBtu
## 1                8591870424
## 2                7460030562
## 3                7945749316
## 4                7712923546
## 5                8437059549
## 6                8800188559
## 7                9583601078
## 8                9981451241
## 9               18636752607
## 10               9347208813
## 11               7968949199
## 12               7849302762
## 13               8216643148
## 14               7353758141
## 15               8000219947
## 16               7333176936
## 17               7948013287
## 18               8477208376
## 19               9577209361
## 20               9514775422
## 21               8305269132
## 22               8815387524
## 23               8038163257
## 24               7911393246
## 25               8227739675
## 26               7422688887
## 27               7720290221
## 28               7985463378
## 29               8204723811
## 30               8642060770
## 31               9283942308
## 32               9894199614
## 33               9024886569
## 34               8507124208
## 35               7930469690
## 36               7952862793
## 37               8278868430
## 38               7474026876
## 39               7241697883
## 40               6388452836
## 41               7199552101
## 42               7692074540
## 43               8351155683
## 44               9051015061
## 45               8626839793
## 46               8417583178
## 47               6745167668
## 48               6862842573
## 49               9201862976
## 50               8219464944
## 51               9089414405
## 52               9621799933
## 53              10322859478
## 54              10718787163

plot_ly() %>% 
  add_trace(
    data = pge_final_GasAndElec_Commercial,
    x = ~Time%>% factor(),
    y = ~Commercial_Gas_kBtu,
    type = "bar",
    name = "Commercial_Gas_Consumption"
  ) %>% 
  add_trace(
    data = pge_final_GasAndElec_Commercial,
    x = ~Time %>% factor(),
    y = ~Commercial_Electric_kBtu,
    type = "bar",
    name = "Commercial_Electricity_Consumption"
  ) %>% 
  layout(
    legend=list(
      x = 0.1, y = 1.0,
      orientation = 'h'),
    title = "Commercial gas/electricity consumption from 2017Q1 to 2021Q2", 
    xaxis = list(
      title = "Month/Year",
      fixedrange = T,
      tickformat  = "%m-%Y",
      type = "date",
      dtick = "M3"
    ),
    yaxis = list(
      title = "kBtu",
      fixedrange = T
    ),
    barmode = "stack"
  ) %>% 
  config(displayModeBar = F)

2 Analysis of observable changes

2.1 Calculate the ratio of commercial to residential energy consumption

pge_analysis_covid<-
  merge(pge_final_GasAndElec_Residential,pge_final_GasAndElec_Commercial,by=c("YEAR","MONTH","Time"))
  arrange(pge_analysis_covid,pge_analysis_covid$YEAR,pge_analysis_covid$MONTH)

##    YEAR MONTH       Time TOTALTHM.x Residential_Gas_kBtu TOTALKWH.x
## 1  2017     1 2017-01-01  332986863          33298816165 2696632298
## 2  2017     2 2017-02-01  214756795          21475763255 2089405296
## 3  2017     3 2017-03-01  167698331          16769898502 2007875145
## 4  2017     4 2017-04-01  128910844          12891134675 1838107707
## 5  2017     5 2017-05-01   93587412           9358777699 2070656073
## 6  2017     6 2017-06-01   74050205           7405049380 2615607826
## 7  2017     7 2017-07-01   70934300           7093457664 3113124827
## 8  2017     8 2017-08-01   69535451           6953572219 2995761408
## 9  2017     9 2017-09-01  136754090          13675462334 5072659238
## 10 2017    10 2017-10-01   92544327           9254468792 1956786021
## 11 2017    11 2017-11-01  156152999          15615360800 2094280651
## 12 2017    12 2017-12-01  292245699          29224683876 2570202596
## 13 2018     1 2018-01-01  256574528          25657552864 2394606529
## 14 2018     2 2018-02-01  225070744          22507162178 1995117494
## 15 2018     3 2018-03-01  216716670          21671751520 2132205702
## 16 2018     4 2018-04-01  126723543          12672403722 1787407410
## 17 2018     5 2018-05-01   99480004           9948039197 1884178568
## 18 2018     6 2018-06-01   77327887           7732818858 2317072842
## 19 2018     7 2018-07-01   70491825           7049209992 3076516808
## 20 2018     8 2018-08-01   73628195           7362848215 2666565449
## 21 2018     9 2018-09-01   76877874           7687817382 2226205087
## 22 2018    10 2018-10-01   91456098           9145645468 1922505661
## 23 2018    11 2018-11-01  183442904          18344361943 2126422448
## 24 2018    12 2018-12-01  274098326          27409939498 2544937320
## 25 2019     1 2019-01-01  272036368          27203742894 2405339756
## 26 2019     2 2019-02-01  295982749          29598390333 2236397294
## 27 2019     3 2019-03-01  210599369          21060019034 2050620632
## 28 2019     4 2019-04-01  115037539          11503798765 1788322259
## 29 2019     5 2019-05-01  111158190          11115862352 1815189861
## 30 2019     6 2019-06-01   75787863           7578815857 2418454504
## 31 2019     7 2019-07-01   73241548           7324183364 2726820259
## 32 2019     8 2019-08-01   70172439           7017271267 2876356186
## 33 2019     9 2019-09-01   73713782           7371406948 2285965349
## 34 2019    10 2019-10-01  109128238          10912866360 1818498345
## 35 2019    11 2019-11-01  188281565          18828229930 2075274460
## 36 2019    12 2019-12-01  270780478          27078153404 2545111200
## 37 2020     1 2020-01-01  297296398          29729755746 2435661356
## 38 2020     2 2020-02-01  213417355          21341818733 1919849451
## 39 2020     3 2020-03-01  210543362          21054418312 2093623179
## 40 2020     4 2020-04-01  147891631          14789220778 1946442584
## 41 2020     5 2020-05-01   98138323           9813870574 2181577771
## 42 2020     6 2020-06-01   80817601           8081791619 2537131639
## 43 2020     7 2020-07-01   80943142           8094345768 2926818249
## 44 2020     8 2020-08-01   76504327           7650462537 3339220306
## 45 2020     9 2020-09-01   77963575           7796387906 2712667506
## 46 2020    10 2020-10-01   97993457           9799383917 2328434857
## 47 2020    11 2020-11-01  218223203          21822405407 2283895788
## 48 2020    12 2020-12-01  302896475          30289765630 2740849821
## 49 2021     1 2021-01-01  297380109          29738126878 2583304201
## 50 2021     2 2021-02-01  223669318          22367019031 2046094821
## 51 2021     3 2021-03-01  227459191          22746007809 2078572237
## 52 2021     4 2021-04-01  133597441          13359796203 2073885444
## 53 2021     5 2021-05-01  102778274          10277867484 2364683776
## 54 2021     6 2021-06-01   78597892           7859819853 2882773155
##    Residential_Electric_kBtu TOTALTHM.y Commercial_Gas_kBtu TOTALKWH.y
## 1                 9200909401   81974539          8197485870 2518133184
## 2                 7129050870   57175524          5717574698 2186409895
## 3                 6850869995   51732816          5173301776 2328765919
## 4                 6271623496   42328501          4232866608 2260528589
## 5                 7065078521   36825348          3682549162 2472760712
## 6                 8924453902   31689281          3168940459 2579187737
## 7                10621981910   30972459          3097257979 2808792813
## 8                10221537924   31294433          3129455505 2925396026
## 9                17307913320   61404848          6140508748 5462119756
## 10                6676553904   38024375          3802452330 2739510203
## 11                7145685581   47704572          4770475805 2335565416
## 12                8769531258   72381506          7238178829 2300499051
## 13                8170397477   68229609          6822987510 2408160360
## 14                6807340890   61167040          6116727855 2155263230
## 15                7275085855   62150804          6215104639 2344730348
## 16                6098634083   45152645          4515282110 2149231224
## 17                6428817274   41023398          4102355799 2329429451
## 18                7905852537   34280865          3428099870 2484527660
## 19               10497075349   32624033          3262416023 2806919508
## 20                9098321312   35658808          3565894707 2788621167
## 21                7595811757   36552669          3655281156 2434135150
## 22                6559589315   44246751          4424692356 2583642299
## 23                7255353393   61713842          6171408268 2355850896
## 24                8683326136   79774028          7977433912 2318696731
## 25                8207019247   85968037          8596837228 2411412566
## 26                7630587567   90504731          9050508397 2175465676
## 27                6996717596   75080567          7508085981 2262687638
## 28                6101755548   53456159          5345636748 2340405445
## 29                6193427806   54414839          5441505122 2404667002
## 30                8251766768   42491414          4249157972 2532843133
## 31                9303910724   42441045          4244121052 2720967851
## 32                9814127307   42087847          4208801114 2899824037
## 33                7799713771   43114274          4311444215 2645042957
## 34                6204716353   58230677          5823090410 2493295489
## 35                7080836458   70930715          7093099163 2324287717
## 36                8683919414   90107471          9010782242 2330850760
## 37                8310476547   99682071          9968245976 2426397547
## 38                6550526327   79910368          7991067965 2190511980
## 39                7143442287   72628696          7262897925 2122420247
## 40                6641262097   48329101          4832928948 1872348428
## 41                7443543355   39984871          3998502694 2110068025
## 42                8656693152   37347568          3734771366 2254418095
## 43                9986303866   41091748          4109190826 2447583729
## 44               11393419684   38181884          3818203291 2652700780
## 45                9255621530   38699899          3870004993 2528382120
## 46                7944619732   45482939          4548311638 2467052514
## 47                7792652429   70066329          7006660226 1976895565
## 48                9351779589   87891802          8789214478 2011384107
## 49                8814233934   87849465          8784980761 2696911775
## 50                6981275529   73948118          7394840640 2408987381
## 51                7092088473   80053860          8005417221 2663954984
## 52                7076097135   58823491          5882372041 2819988257
## 53                8068301044   50407495          5040769159 3025457057
## 54                9836022005   42960752          4296091955 3141496824
##    Commercial_Electric_kBtu
## 1                8591870424
## 2                7460030562
## 3                7945749316
## 4                7712923546
## 5                8437059549
## 6                8800188559
## 7                9583601078
## 8                9981451241
## 9               18636752607
## 10               9347208813
## 11               7968949199
## 12               7849302762
## 13               8216643148
## 14               7353758141
## 15               8000219947
## 16               7333176936
## 17               7948013287
## 18               8477208376
## 19               9577209361
## 20               9514775422
## 21               8305269132
## 22               8815387524
## 23               8038163257
## 24               7911393246
## 25               8227739675
## 26               7422688887
## 27               7720290221
## 28               7985463378
## 29               8204723811
## 30               8642060770
## 31               9283942308
## 32               9894199614
## 33               9024886569
## 34               8507124208
## 35               7930469690
## 36               7952862793
## 37               8278868430
## 38               7474026876
## 39               7241697883
## 40               6388452836
## 41               7199552101
## 42               7692074540
## 43               8351155683
## 44               9051015061
## 45               8626839793
## 46               8417583178
## 47               6745167668
## 48               6862842573
## 49               9201862976
## 50               8219464944
## 51               9089414405
## 52               9621799933
## 53              10322859478
## 54              10718787163

pge_combined_analysis_covid <-
  pge_analysis_covid %>% 
  select(
    c("Commercial_Electric_kBtu",
      "Residential_Electric_kBtu",
      "Commercial_Gas_kBtu",
      "Residential_Gas_kBtu",
       "YEAR",
       "MONTH",
       "Time"))%>% 
  mutate(
    total_commercial_consumption = Commercial_Gas_kBtu + Commercial_Electric_kBtu,
    total_residential_consumption = Residential_Gas_kBtu + Residential_Electric_kBtu,
    Ratio_of_commercial_to_residential_consumption = 
      total_commercial_consumption/
      total_residential_consumption,
    Ratio_of_gas_to_elec_commercial = 
      Commercial_Gas_kBtu/
      Commercial_Electric_kBtu,
    Ratio_of_gas_to_elec_residential = 
      Residential_Gas_kBtu/
      Residential_Electric_kBtu,
    total_gas_residentialAndcommercial = 
      Residential_Gas_kBtu/
      Commercial_Gas_kBtu,
    total_electric_residentialAndcommercial = 
      Residential_Electric_kBtu/
      Commercial_Electric_kBtu,
    )
  
pge_combined_analysis_covid<-
  arrange(pge_combined_analysis_covid,pge_combined_analysis_covid$YEAR,pge_combined_analysis_covid$MONTH)

plot_ly(pge_combined_analysis_covid,
        x = ~as.Date(Time),
        y = ~Ratio_of_commercial_to_residential_consumption,
        type = "scatter",
        mode = "lines",
        name = "Ratio of commercial to residential energy consumption") %>% 
  add_trace(x =as.Date("2020-3-11"),type = 'scatter', mode = 'lines',
            line = list(color = 'red'),name = 'Date of COVID-19 pandemic began according to WHO') %>%
  layout(
    #legend = list(title = list(text = "Description")),
    title = "Ratio of commercial to residential energy consumption from 2017Q1 to 2021Q2", 
    xaxis = list(
      title = "Month/Year",
      fixedrange = T,
      tickformat  = "%m-%Y",
      type = "date",
      dtick = "M3"
    ),
    yaxis = list(
      title = "Ratio",
      fixedrange = T
    )
  ) %>% 
  config(displayModeBar = F)

*The World Health Organization (WHO) declared a Public Health Emergency of International Concern on 30 January 2020, and a pandemic on 11 March 2020.

plot_ly(pge_combined_analysis_covid,
        x = ~as.Date(Time),
        y = ~total_gas_residentialAndcommercial,
        type = "bar",
        name = "total gas consumption - residential and commercial") %>% 
  add_trace(x =as.Date("2020-3-11"),type = 'scatter', mode = 'lines',
            line = list(color = 'red'),name = 'Date of COVID-19 pandemic began according to WHO') %>%
  layout(
    #legend = list(title = list(text = "Description")),
    title = "total gas consumption - residential and commercial - from 2017Q1 to 2021Q2", 
    xaxis = list(
      title = "Month/Year",
      fixedrange = T,
      tickformat  = "%m-%Y",
      type = "date",
      dtick = "M3"
    ),
    yaxis = list(
      title = "Ratio",
      fixedrange = T
    )
  ) %>% 
  config(displayModeBar = F)

plot_ly(pge_combined_analysis_covid,
        x = ~as.Date(Time),
        y = ~total_electric_residentialAndcommercial,
        type = "bar",
        name = "total electricity consumption - residential and commercial") %>% 
  add_trace(x =as.Date("2020-3-11"),type = 'scatter', mode = 'lines',
            line = list(color = 'red'),name = 'Date of COVID-19 pandemic began according to WHO') %>%
  layout(
    #legend = list(title = list(text = "Description")),
    title = "total electricity consumption - residential and commercial - from 2017Q1 to 2021Q2", 
    xaxis = list(
      title = "Month/Year",
      fixedrange = T,
      tickformat  = "%m-%Y",
      type = "date",
      dtick = "M3"
    ),
    yaxis = list(
      title = "Ratio",
      fixedrange = T
    )
  ) %>% 
  config(displayModeBar = F)

2.2 Comment on observable changes in energy consumption that may be attributable to the COVID-19 pandemic

1. In the chart “Ratio of commercial to residential energy consumption”, I used the red line to mark the time when WHO declared a pandemic (according to WHO). It can be seen that after this time point, the ratio of commercial energy consumption to residential energy consumption has dropped significantly. This is in line with our perception that due to the epidemic, people spend more time at home instead of in commercial buildings.

2. Starting from the first quarter of 2021, the ratio of commercial energy consumption to residential energy consumption has rebounded significantly. This may be due to the liberalization of restrictions on the COVID-19 in various regions. The implementation of the epidemic control policy is no longer that mandatory. More people choose to return to commercial buildings. As of the second quarter of 2021, this ratio is basically the same as the second quarter of 2019. Compared with the same period before the pandemic, there was even a slight improvement.

3. According to the chart “total gas consumption - residential and commercial”, since the first quarter of 2017, the gas consumption of commercial and residential buildings has been showing a downward trend. However, after the outbreak of the epidemic, this downward trend no longer continued, and the total gas consumption increased.

4. According to the chart “total electricity consumption - residential and commercial”, since the outbreak of the epidemic, total electricity consumption has risen significantly. This may be due to the significant increase in the amount of time people spend indoors during the epidemic, and the decrease in outdoor activities, leading to an increase in electricity consumption. Starting from the first quarter of 2021, such high-level electricity consumption has dropped again. This may be because the impact of the epidemic on people’s normal lives has been significantly reduced, and the total electricity consumption has basically fallen to the level before the epidemic.